package utils.fileConvert;

import com.alibaba.fastjson.JSON;
import com.google.common.collect.ImmutableMap;
import org.apache.poi.ss.usermodel.*;
import utils.data.DataTypeConvertUtil;
import utils.string.StringUtil;

import java.io.File;
import java.io.FileInputStream;
import java.util.*;

/**
 * @author Chen Yuan
 * @create 2021-08-30  17:03
 */
public class CreateSQLByExcel {

	public static void main(String[] args) {
		String path = "C:\\Users\\zovan\\Desktop\\新建XLS 工作表.xls";
		//createSQLByExcelTable("cpath_ta_phase_image_d", path);
		CreateSQLByExcel.createDTOClassByExcelTable(path);
		//CreateSQLByExcel.createClickHouseByExcelTable("outp_clinic_registration", path);
		//CreateSQLByExcel.createVOByExcelTable(path);
	}


	private static final List noLengthType = Arrays.asList("DATETIME", "BIGINT");

	public static void createSQLByExcelTable(String tableName, String excelPath) {
		List<List<Object>> rowList = readExcel(excelPath,
				0, 0, 1, 2, 7, 8);
		System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>");
		StringBuilder builder = new StringBuilder();
		builder.append("CREATE TABLE `" + tableName + "` ( \n");
		builder.append("`id` varchar(100) COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键' PRIMARY KEY, \n");
		for (int i = 0; i < rowList.size(); i++) {
			String sql = createSQL(rowList.get(i), i + 1);
			builder.append(sql);
			if (i < rowList.size() - 1) {
				builder.append(", ");
			}
			builder.append("\n");
		}
		builder.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;");

		System.out.println("<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<");
		System.out.println("\n");
		System.out.println(builder.toString());
	}

	public static void createClickHouseByExcelTable(String tableName, String excelPath) {
		List<List<Object>> rowList = readExcel(excelPath, 1, 1, 3, 6, 6, 6);
		System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>");
		StringBuilder builder = new StringBuilder();
		builder.append("CREATE TABLE shennong.").append(tableName).append(" on cluster clickhouse_cluster ( \n");

		for (int i = 0; i < rowList.size(); i++) {
			String sql = rowList.get(i).get(0) + " " + rowList.get(i).get(1);
			builder.append(sql);
			if (i < rowList.size() - 1) {
				builder.append(", ");
			}
			builder.append("\n");
		}
		builder.append(")ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/shennong.").append(tableName).append("', '{replica}')\n")
				.append("PARTITION BY id\n")
				.append("ORDER BY id\n")
				.append("SAMPLE BY id\n")
				.append("SETTINGS index_granularity = 8192;");

		System.out.println("<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<");
		System.out.println("\n");
		System.out.println(builder.toString());
	}

	public static void createVOByExcelTable(String excelPath) {
		List<List<Object>> rowList = readExcel(excelPath, 1, 1, 2, 6, 4, 0);
		System.out.println(rowList.toString());
		System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>");
		StringBuilder builder = new StringBuilder();

		for (int i = 0; i < rowList.size(); i++) {
			if (null != rowList.get(i).get(4) && !"".equals(rowList.get(i).get(4).toString())) {
				builder.append("//----------- ").append(rowList.get(i).get(4)).append(" ---------------").append("\n");
			}
			if (null != rowList.get(i).get(3) && !"".equals(rowList.get(i).get(3).toString())) {
				builder.append("/** ").append(rowList.get(i).get(3)).append(" */").append("\n");
			}

			builder.append("private ").
					append(DataTypeConvertUtil.convertToJavaType(String.valueOf(rowList.get(i).get(1)))).
					append(" ").
					append(StringUtil.lineToHump(String.valueOf(rowList.get(i).get(0)))).
					append(";");
			builder.append("\n\n");
		}

		System.out.println("<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<");
		System.out.println("\n");
		System.out.println(builder.toString());
	}

	private static String createSQL(List<Object> cellList, int index) {
		StringBuilder builder = new StringBuilder();

		Boolean isNotNull = judgeNull(cellList, index);
		if (!isNotNull) {
			return "";
		}

		// 拼接列名
		builder.append("`").append(cellList.get(0).toString().toLowerCase()).append("` ");

		// 拼接类型 和 长度
		String type = cellList.get(1).toString();
		String length = cellList.get(2).toString();
		String typeStr = "";
		String lengthStr = "";
		if (type.contains("字符")) {
			int lenght1 = Integer.parseInt(length);
			if (lenght1 <= 500) {
				typeStr = "varchar";
			} else {
				typeStr = "text";
			}
			lengthStr = "(" + (lenght1 + 20) + ") COLLATE utf8mb4_general_ci ";
		} else if (type.contains("数值")) {
			length = length.replace("，", ",");
			String[] split = length.split(",");
			if (split.length == 1) {
				typeStr = "int";
				int lenght1 = Integer.parseInt(split[0]);
				lengthStr = "(" + (lenght1 + 10) + ") ";
			} else if (split.length == 2) {
				typeStr = "decimal";
				lengthStr = "(" + split[0] + ", " + split[1] + ") ";
			}
		} else if (type.contains("日期")) {
			typeStr = "datetime ";
		} else {
			typeStr = type.toUpperCase();
			lengthStr = (noLengthType.contains(typeStr) || null == length || "".equals(length)) ? "" : "(" + (Integer.parseInt(length)) + ") ";
			//System.out.println("第 "+(index)+" 行 - 类型出错");
		}
		builder.append(typeStr).append(" ").append(lengthStr);

		builder.append("DEFAULT NULL ");

		// 拼接说明
		builder.append("COMMENT '").append(cellList.get(3) + "").append(cellList.get(4).toString().trim()).append("'");

		//System.out.println(builder.toString());
		return builder.toString();
	}

	public static void createDTOClassByExcelTable(String excelPath) {
		List<List<Object>> rowList = readExcel(excelPath,
				0, 1, 3, 3, 0, 5);
		System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>");
		StringBuilder builder = new StringBuilder();
		int index = 0;
		for (List<Object> row : rowList) {
			builder.append(CreateSQLByExcel.createDTOClass(row, index)).append("\n\n");
			index++;
		}
		System.out.println(builder.toString());
	}

	private static String createDTOClass(List<Object> cellList, int index) {
		StringBuilder builder = new StringBuilder();

		Boolean isNotNull = judgeNull(cellList, index);
		if (!isNotNull) {
			return "";
		}
		ImmutableMap<String, String> typeMap = ImmutableMap.of("String", "String", "Number", "Integer");
		String type = (null == typeMap.get(String.valueOf(cellList.get(1)))) ? cellList.get(1).toString() : typeMap.get(String.valueOf(cellList.get(1)));

		builder.append("/** ");
		if (!"".equals(cellList.get(3).toString())) {
			builder.append(cellList.get(3)).append("; ");
		}
		if (!"".equals(cellList.get(4).toString())) {
			builder.append(cellList.get(4)).append("; ");
		}

		return builder.append(" */").append("\n").append("private ").append(type).append(" ").append(cellList.get(0)).append(";").toString();
	}


	public static Boolean judgeNull(List<Object> cellList, int index) {
		if (cellList.get(0) == null) {
			System.out.println("第 " + (index) + " 行 - 参数代码出错");
			return false;
		}
		if (cellList.get(1) == null) {
			System.out.println("第 " + (index) + " 行 - 类型出错");
			return false;
		}
		//if (cellList.get(2) == null){
		//	System.out.println("第 "+(index)+" 行 - 长度出错");
		//	return false;
		//}
		//if (cellList.get(3) == null){
		//	System.out.println("第 "+(index)+" 行 - 参数名称出错");
		//	return false;
		//}
		//if (cellList.get(4) == null){
		//	System.out.println("第 "+(index)+" 行 - 说明出错");
		//	return false;
		//}
		return true;
	}

	/**
	 * 读取 Excel
	 *
	 * @param path       路径
	 * @param firstRow   Execl 中有数据的第一行在 的行数，从 0 开始
	 * @param columnCell 参数代码所在列，从 0 开始
	 * @param typeCell   数据类型所在列，从 0 开始
	 * @param lengthCell 数据长度所在列，从 0 开始
	 * @param nameCell   参数名称所在列，从 0 开始
	 * @param memoCell   说明所在列，从 0 开始
	 */
	public static List<List<Object>> readExcel(String path, Integer firstRow, Integer columnCell,
	                                           Integer typeCell, Integer lengthCell,
	                                           Integer nameCell, Integer memoCell) {
		List<List<Object>> result = new ArrayList<>();
		if (path == null || path == "") {
			return result;
		}
		File file = new File(path);
		FileInputStream fis = null;
		Workbook workBook = null;
		if (file.exists()) {
			try {
				fis = new FileInputStream(file);
				workBook = WorkbookFactory.create(fis);
				int numberOfSheets = workBook.getNumberOfSheets();
				// sheet工作表
				for (int s = 0; s < numberOfSheets; s++) {
					Sheet sheetAt = workBook.getSheetAt(s);
					//获取工作表名称
					String sheetName = sheetAt.getSheetName();
					//System.out.println("工作表名称：" + sheetName);
					// 获取当前Sheet的总行数
					int rowsOfSheet = sheetAt.getPhysicalNumberOfRows();
					System.out.println("当前表格的总行数:" + rowsOfSheet);
					if (rowsOfSheet <= 0) {
						continue;
					}
					for (int r = firstRow; r < rowsOfSheet; r++) {
						ArrayList<Object> rowList = new ArrayList<>();
						Row row = sheetAt.getRow(r);
						if (row == null ||
								(null == row.getCell(columnCell) &&
										null == row.getCell(typeCell) &&
										null == row.getCell(lengthCell) &&
										null == row.getCell(nameCell) &&
										null == row.getCell(memoCell))) {
							continue;
						} else {
							int rowNum = row.getRowNum() + 1;
							Cell cell1 = row.getCell(columnCell);
							cell1.setCellType(CellType.STRING);
							Cell cell2 = row.getCell(typeCell);
							cell2.setCellType(CellType.STRING);
							Cell cell3 = row.getCell(lengthCell);
							cell3.setCellType(CellType.STRING);
							Cell cell4 = row.getCell(nameCell);
							cell4.setCellType(CellType.STRING);
							Cell cell5 = row.getCell(memoCell);
							cell5.setCellType(CellType.STRING);
							//System.out.println("当前行:" + rowNum + ", " + cell1 + ", " + cell2 + ", " + cell3 + ", " + cell4 + ", " + cell5 + "\n");

							rowList.add(cell1);// 列代码
							rowList.add(cell2);// 数据类型
							rowList.add(cell3);// 长度
							rowList.add(cell4);// 列名称
							rowList.add(cell5);// 注释
							//System.out.println(rowList);
						}
						result.add(rowList);
					}
				}
				if (fis != null) {
					fis.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		} else {
			System.out.println("文件不存在!");
		}
		return result;
	}
}
